Computerized Investing - Spreadsheet Collection/Spreadsheet Collection.iso/pc/ibm11/ytm.doc

Text File | 1991-07-12 | 5.5 KB | 121 lines

July/August 1989, Computerized Investing
A True Yield to Maturity Calculation Macro
by Ed Garner with Fred Shipley

In this column we give you a means of determining a bond's
true yield to maturity (YTM) for the bond management
worksheet that appeared in the January/February issue of
Computerized Investing (BONDMGMT.WKS).  It can also be ap-
plied to the bond section of the portfolio management
worksheet (PORTMGMT.WKS on disk 12 and BBS) that appeared in
the March/April issue. When we cite the "original"
spreadsheet here, however, we will be referring to the
Jan./Feb. bond management worksheet. The macro iterates a
number of times to get the true yield.

In creating this macro, we have added an extra column or two
of calculations to make summing totals a little easier. By
using the @SUM function in 1-2-3, the figures will still be
correct when new rows, representing new bonds, are added to
the portfolio. These calculations can also be added to the
portfolio management worksheet. The new versions will be
loaded on the CI BBS.

Area for Additional Calculations

To set up the spreadsheet to handle these calculations, you
must add a few columns of data to the right of the existing
calculations area, which extends to cell AE. These columns
perform preliminary calculations so that the total initial
cost (column AF) and the average maturity (column AG) may be
computed by summing the values in these columns. When a new
bond is added to the worksheet, or a bond is deleted because
it has been sold, the summation formula, @SUM, remains valid.

Originally, we programmed the formula to determine the
weighted average maturity by taking the maturity of each bond
and multiplying it by the percentage of the portfolio that
the bond comprises. This formula does not adjust when you add
or delete a row as a result of bond transactions. To get
around this problem, simply add a column where these
multiplications can be carried out. Then you can use the @SUM
function on those rows. When you insert a row for a new bond,
or delete a row because you have sold a bond, the @SUM
function remains accurate.

A True Yield Calculation for Zero Coupon Issues

In the original spreadsheet, we used the same approximation
formula for yield to maturity for both the coupon-paying and
zero coupon issues. Since we are determining an exact yield
for the coupon-paying issues, we must also determine an exact
yield for any zeros in our portfolio. The iterative macro
will not work for zeros since there is no periodic coupon.

Since a zero coupon bond has only one payment at maturity, it
is not difficult to use the formula for determining the
present value of that payment. That formula is:

AK33: ((100/$G$33)^(1/(($D33-@DATE($D$4-1900,$C$4,$B$4))/
365.25))-1)/2

This formula first determines the ratio of the future value
of the bond to its current price. The future value is then
discounted by a power equal to the number of years until the
bond matures. The final number is divided by 2 to put the
yield on a semiannual basis. This formula should be copied to
cell AI33.

How the Macro Works

A macro is just a series of commands that 1-2-3 recognizes
and executes automatically. Macros are given a letter name,
and we use the letter Y for yield. Macros are started by
holding down the ALT key and the letter of the macro
simultaneously. This combination is indicated by ALT-Y. To
give the macro a name, use the /Range Name command. If you
enter '\Y into cell A41, you can use the /Range Name Label
Right command to automatically give cell B41 the name \Y. 1-
2-3 recognizes the backslash as a symbol for the ALT key. A
macro implements the commands in cell B41 and any non-blank
cell below B41 until it reaches a blank line. At that point
the macro stops executing commands.

The {windowsoff} command turns off the redisplay of the
current windows.  This command is unique to Release 2 of 1-2-
3; its purpose is to speed up the recalculation process. Then
the macro uses the approximation formula in column AI to get
a starting estimate for the true yield to maturity. The value
of this formula is then copied to the column labeled Est YTM
(column AJ). Another calculation is performed and an average
of the two yields calculated in column AL. This average is
copied to column AJ, Est YTM, and the procedure iterates
again. The process is repeated 15 times, using the FOR
command. The FOR command has the following syntax:

{FOR,counter,start,stop,step,MACRO}

This loops through MACRO, starting with the step indicated by
start, stopping at the step indicated by stop, and
incrementing the amount indicated by step, using the location
indicated by counter as the means of keeping track of the
iterations. Our loop has the form:

{for COUNTER,1,15,1,CALC}.

This loops through the macro called CALC 15 times in steps of
1. The cell labeled COUNTER is reset to 16 after completion
of the macro. If you were to view that cell you would see it
change as the macro iterated.

You can check to see if you have the true yield to maturity
by observing if the values in columns AJ, AK and AL are the
same. If so, you have determined the true yield to maturity.
If not, invoke the macro ALT-I, which is part of the original
macro, to do another 15 calculations. Except for bonds
selling at great differences from par, 15 iterations should
be sufficient. The {windowson} command resets the spreadsheet
to its default setting when the macro is complete.

(C) Copyright 1991 by the
American Association of Individual Investors